package cn.iocoder.yudao.module.promotion.groupon.dao;

import cn.iocoder.yudao.framework.business.basic.dao.mapper.CoreMapper;
import cn.iocoder.yudao.framework.business.basic.dao.query.CoreQueryWrapper;
import cn.iocoder.yudao.module.promotion.groupon.pojo.bo.ShopIdDistanceBO;
import cn.iocoder.yudao.module.promotion.groupon.pojo.bo.ShopIdPackageBO;
import cn.iocoder.yudao.module.promotion.groupon.pojo.po.GrouponPackage;
import cn.iocoder.yudao.module.promotion.groupon.pojo.po.GrouponProduct;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Collection;
import java.util.List;

/**
 * <pre>
 * OOoO0OOoO0OOOooo0oOOOO0OOOOO0oooOO0ooOOO0Ooooo0OOOOo0ooooO0OOooo0Ooooo0OOOOO
 *  这里是文件说明
 * OOoO0OOoO0OOOooo0oOOOO0OOOOO0oooOO0ooOOO0Ooooo0OOOOo0ooooO0OOooo0Ooooo0OOOOO
 * </pre>
 *
 * @author 山野羡民（1032694760@qq.com）
 * @since 2025/01/13
 */
@Mapper
public interface GrouponPackageMapper extends CoreMapper<GrouponPackage> {

    default void deleteById(Long id, Long shopId) {
        CoreQueryWrapper<GrouponProduct> wrapper = new CoreQueryWrapper<>();
        deleteById(wrapper.eq("id", id).eq("shop_id", shopId));
    }

    // 外部查询筛选出排名小于 limit 的记录，即每个店铺最多 limit 条数据。
    // 内部查询中，使用子查询计算每个记录在其所属店铺中的排名，排名是通过计算在当前记录之前有多少条记录（按 price 和 id 降序排列）来确定的。
    @Select("""
            <script>
            SELECT shop_id, name, min_people, max_people, price, vip_price, origin_price
            FROM (
                SELECT shop_id, name, min_people, max_people, price, vip_price, origin_price,
                (SELECT COUNT(1) FROM promotion_groupon_package p2
                WHERE deleted = FALSE AND p2.shop_id = p1.shop_id
                      AND (p2.price <![CDATA[ < ]]> p1.price OR (p2.price = p1.price AND p2.id <![CDATA[ > ]]> p1.id))
                ) as rankIndex
                FROM promotion_groupon_package p1
                WHERE deleted = FALSE AND shop_id IN
                <foreach item="id" collection="shopIds" open="(" separator="," close=")">
                    #{id}
                </foreach>
            ) subquery
            WHERE rankIndex <![CDATA[ < ]]> #{limit}
            ORDER BY shop_id, rankIndex DESC;
            </script>
            """)
    List<GrouponPackage> selectSimpleListByShopIds(@Param("shopIds") Collection<Long> shopIds, @Param("limit") int limit);

    @Select("""
            SELECT si.id AS shop_id
            FROM shop_info si
            JOIN promotion_groupon_package pgp ON si.id = pgp.shop_id
            WHERE si.deleted = FALSE AND pgp.deleted = FALSE
            ORDER BY pgp.id DESC;
            """)
    List<Long> selectShopIdsOrderById();

    @Select("""
            SELECT si.id AS shop_id
            FROM shop_info si
            JOIN promotion_groupon_package pgp ON si.id = pgp.shop_id
            WHERE si.deleted = FALSE AND pgp.deleted = FALSE
            ORDER BY RAND();
            """)
    List<Long> selectShopIdsOrderByRandom();

    @Select("""
            SELECT si.id AS shop_id
            FROM shop_info si
            JOIN promotion_groupon_package pgp ON si.id = pgp.shop_id
            WHERE si.deleted = FALSE AND pgp.deleted = FALSE
            ORDER BY pgp.price;
            """)
    List<Long> selectShopIdsOrderByPrice();

    @Select("""
            SELECT si.id AS shop_id
            FROM shop_info si
            JOIN promotion_groupon_package pgp ON si.id = pgp.shop_id
            WHERE si.deleted = FALSE AND pgp.deleted = FALSE
            ORDER BY si.score DESC;
            """)
    List<Long> selectShopIdsOrderByScore();

    @Select("""
            SELECT
                si.id AS shop_id,
                ROUND(
                    6371 * ACOS(
                        COS(RADIANS(si.latitude)) * COS(RADIANS(#{latitude})) *
                        COS(RADIANS(si.longitude) - RADIANS(#{longitude})) +
                        SIN(RADIANS(si.latitude)) * SIN(RADIANS(#{latitude}))
                    ), 2
                ) AS distance_km
            FROM shop_info si
            JOIN promotion_groupon_package pgp ON si.id = pgp.shop_id
            WHERE si.deleted = FALSE AND pgp.deleted = FALSE
            ORDER BY distance_km;
            """)
    List<ShopIdDistanceBO> selectShopIdsAndDistance(@Param("longitude") Double longitude, @Param("latitude") Double latitude);

    @Select("""
            <script>
                SELECT shop_id, id AS package_id
                FROM promotion_groupon_package
                WHERE deleted = FALSE AND shop_id IN
                <foreach item="id" collection="shopIds" open="(" separator="," close=")">
                    #{id}
                </foreach>
            </script>
            """)
    List<ShopIdPackageBO> selectByShopIds(@Param("shopIds") Collection<Long> shopIds);

}
